<!-- build time:Tue Jul 21 2020 12:41:23 GMT+0800 (GMT+08:00) --><!DOCTYPE html><html lang="zh-CN"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1,maximum-scale=2"><meta name="theme-color" content="#222"><meta name="generator" content="Hexo 4.2.0"><link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png"><link rel="icon" type="image/png" sizes="32x32" href="/img/blog_favicon/favicon-32x32-next.png"><link rel="icon" type="image/png" sizes="16x16" href="/img/blog_favicon/favicon-16x16-next.png"><link rel="mask-icon" href="/images/logo.svg" color="#222"><meta name="baidu-site-verification" content="r15J5zOS2KI43yBS"><link rel="stylesheet" href="/css/main.css"><link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css"><link rel="stylesheet" href="//cdn.jsdelivr.net/gh/fancyapps/fancybox@3/dist/jquery.fancybox.min.css"><link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/pace/1.0.2/themes/blue/pace-theme-minimal.min.css"><script src="//cdnjs.cloudflare.com/ajax/libs/pace/1.0.2/pace.min.js"></script><script id="hexo-configurations">var NexT=window.NexT||{},CONFIG={hostname:new URL("http://www.zhengjiaao.cn").hostname,root:"/",scheme:"Muse",version:"7.6.0",exturl:!1,sidebar:{position:"left",display:"post",padding:18,offset:12,onmobile:!1},copycode:{enable:!0,show_result:!0,style:null},back2top:{enable:!0,sidebar:!1,scrollpercent:!0},bookmark:{enable:!0,color:"#222",save:"auto"},fancybox:!0,mediumzoom:!1,lazyload:!1,pangu:!1,comments:{style:"tabs",active:"valine",storage:!0,lazyload:!1,nav:null,activeClass:"valine"},algolia:{appID:"",apiKey:"",indexName:"",hits:{per_page:10},labels:{input_placeholder:"Search for Posts",hits_empty:"We didn't find any results for the search: ${query}",hits_stats:"${hits} results found in ${time} ms"}},localsearch:{enable:!0,trigger:"auto",top_n_per_article:1,unescape:!1,preload:!1},path:"search.xml",motion:{enable:!0,async:!1,transition:{post_block:"fadeIn",post_header:"slideDownIn",post_body:"slideDownIn",coll_header:"slideLeftIn",sidebar:"slideUpIn"}}}</script><meta property="og:type" content="article"><meta property="og:title" content="表空间、用户、Schema"><meta property="og:url" content="http://www.zhengjiaao.cn/posts/3827455956.html"><meta property="og:site_name" content="宇宙小神特别萌"><meta property="og:locale" content="zh_CN"><meta property="og:image" content="https://upload-images.jianshu.io/upload_images/15645795-c2db5d64eb91a853.png"><meta property="og:image" content="https://upload-images.jianshu.io/upload_images/15645795-68514643c2a9df3e.png"><meta property="og:image" content="https://upload-images.jianshu.io/upload_images/15645795-b0185f47ea57b3b7.png"><meta property="og:image" content="https://upload-images.jianshu.io/upload_images/15645795-60c67264ca681c2e.png"><meta property="article:published_time" content="2020-01-03T08:14:51.000Z"><meta property="article:modified_time" content="2020-07-20T10:37:55.330Z"><meta property="article:author" content="宇宙小神特别萌"><meta property="article:tag" content="Oracle"><meta name="twitter:card" content="summary"><meta name="twitter:image" content="https://upload-images.jianshu.io/upload_images/15645795-c2db5d64eb91a853.png"><link rel="canonical" href="http://www.zhengjiaao.cn/posts/3827455956.html"><script id="page-configurations">CONFIG.page={sidebar:"",isHome:!1,isPost:!0}</script><title>表空间、用户、Schema | 宇宙小神特别萌</title><noscript><style>.sidebar-inner,.use-motion .brand,.use-motion .collection-header,.use-motion .comments,.use-motion .menu-item,.use-motion .pagination,.use-motion .post-block,.use-motion .post-body,.use-motion .post-header{opacity:initial}.use-motion .site-subtitle,.use-motion .site-title{opacity:initial;top:initial}.use-motion .logo-line-before i{left:initial}.use-motion .logo-line-after i{right:initial}</style></noscript><link rel="alternate" href="/atom.xml" title="宇宙小神特别萌" type="application/atom+xml"></head><body itemscope itemtype="http://schema.org/WebPage"><div class="container use-motion"><div class="headband"></div><header class="header" itemscope itemtype="http://schema.org/WPHeader"><div class="header-inner"><div class="site-brand-container"><div class="site-meta custom-logo"><div class="site-meta-headline"><a><img class="custom-logo-image" src="/img/blog_favicon/favicon-32x32-next.png" alt="宇宙小神特别萌"></a></div><div><a href="/" class="brand" rel="start"><span class="logo-line-before"><i></i></span> <span class="site-title">宇宙小神特别萌</span> <span class="logo-line-after"><i></i></span></a></div><p class="site-subtitle">ZhengJa</p></div><div class="site-nav-toggle"><div class="toggle" aria-label="切换导航栏"><span class="toggle-line toggle-line-first"></span> <span class="toggle-line toggle-line-middle"></span> <span class="toggle-line toggle-line-last"></span></div></div></div><nav class="site-nav"><ul id="menu" class="menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-fw fa-home"></i>首页</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-fw fa-tags"></i>标签<span class="badge">27</span></a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-fw fa-archive"></i>归档<span class="badge">116</span></a></li><li class="menu-item menu-item-sitemap"><a href="/sitemap.xml" rel="section"><i class="fa fa-fw fa-sitemap"></i>站点地图</a></li><li class="menu-item menu-item-messageboard"><a href="/messageboard/" rel="section"><i class="fa fa-fw fa-address-book"></i>留言板</a></li><li class="menu-item menu-item-links"><a href="/links/" rel="section"><i class="fa fa-fw fa-user-plus"></i>友链</a></li><li class="menu-item menu-item-shuoshuo"><a href="/shuoshuo/" rel="section"><i class="fa fa-fw fa-file-text-o"></i>说说</a></li><li class="menu-item menu-item-search"><a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索</a></li></ul></nav><div class="site-search"><div class="popup search-popup"><div class="search-header"><span class="search-icon"><i class="fa fa-search"></i></span><div class="search-input-container"><input autocomplete="off" autocorrect="off" autocapitalize="none" placeholder="搜索..." spellcheck="false" type="text" id="search-input"></div><span class="popup-btn-close"><i class="fa fa-times-circle"></i></span></div><div id="search-result"></div></div><div class="search-pop-overlay"></div></div></div></header><div class="back-to-top"><i class="fa fa-arrow-up"></i> <span>0%</span></div><div class="reading-progress-bar"></div><a role="button" class="book-mark-link book-mark-link-fixed"></a> <a href="https://github.com/zhengjiaao" class="github-corner" title="Follow me on GitHub" aria-label="Follow me on GitHub" rel="noopener" target="_blank"><svg width="80" height="80" viewBox="0 0 250 250" aria-hidden="true"><path d="M0,0 L115,115 L130,115 L142,142 L250,250 L250,0 Z"></path><path d="M128.3,109.0 C113.8,99.7 119.0,89.6 119.0,89.6 C122.0,82.7 120.5,78.6 120.5,78.6 C119.2,72.0 123.4,76.3 123.4,76.3 C127.3,80.9 125.5,87.3 125.5,87.3 C122.9,97.6 130.6,101.9 134.4,103.2" fill="currentColor" style="transform-origin:130px 106px" class="octo-arm"></path><path d="M115.0,115.0 C114.9,115.1 118.7,116.5 119.8,115.4 L133.7,101.6 C136.9,99.2 139.9,98.4 142.2,98.6 C133.8,88.0 127.5,74.4 143.8,58.0 C148.5,53.4 154.0,51.2 159.7,51.0 C160.3,49.4 163.2,43.6 171.4,40.1 C171.4,40.1 176.1,42.5 178.8,56.2 C183.1,58.6 187.2,61.8 190.9,65.4 C194.5,69.0 197.7,73.2 200.1,77.6 C213.8,80.2 216.3,84.9 216.3,84.9 C212.7,93.1 206.9,96.0 205.4,96.6 C205.1,102.4 203.0,107.8 198.3,112.5 C181.9,128.9 168.3,122.5 157.7,114.1 C157.9,116.9 156.7,120.9 152.7,124.9 L141.0,136.5 C139.8,137.7 141.6,141.9 141.8,141.8 Z" fill="currentColor" class="octo-body"></path></svg></a><main class="main"><div class="main-inner"><div class="content-wrap"><div class="content"><div class="posts-expand"><article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN"><link itemprop="mainEntityOfPage" href="http://www.zhengjiaao.cn/posts/3827455956.html"><span hidden itemprop="author" itemscope itemtype="http://schema.org/Person"><meta itemprop="image" content="/my-images/avatar.gif"><meta itemprop="name" content="宇宙小神特别萌"><meta itemprop="description" content="人生就是一次幸运的旅行！"></span><span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization"><meta itemprop="name" content="宇宙小神特别萌"></span><header class="post-header"><h1 class="post-title" itemprop="name headline">表空间、用户、Schema</h1><div class="post-meta"><span class="post-meta-item"><span class="post-meta-item-icon"><i class="fa fa-calendar-o"></i> </span><span class="post-meta-item-text">发表于</span> <time title="创建时间：2020-01-03 16:14:51" itemprop="dateCreated datePublished" datetime="2020-01-03T16:14:51+08:00">2020-01-03</time> </span><span class="post-meta-item"><span class="post-meta-item-icon"><i class="fa fa-calendar-check-o"></i> </span><span class="post-meta-item-text">更新于</span> <time title="修改时间：2020-07-20 18:37:55" itemprop="dateModified" datetime="2020-07-20T18:37:55+08:00">2020-07-20</time> </span><span id="/posts/3827455956.html" class="post-meta-item leancloud_visitors" data-flag-title="表空间、用户、Schema" title="热度"><span class="post-meta-item-icon"><i class="fa fa-eye"></i> </span><span class="post-meta-item-text">热度：</span> <span class="leancloud-visitors-count"></span> <span>℃</span> </span><span class="post-meta-item" title="热度" id="busuanzi_container_page_pv" style="display:none"><span class="post-meta-item-icon"><i class="fa fa-eye"></i> </span><span class="post-meta-item-text">热度：</span> <span id="busuanzi_value_page_pv"></span> </span><span class="post-meta-item"><span class="post-meta-item-icon"><i class="fa fa-comment-o"></i> </span><span class="post-meta-item-text">Valine：</span> <a title="valine" href="/posts/3827455956.html#comments" itemprop="discussionUrl"><span class="post-comments-count valine-comment-count" data-xid="/posts/3827455956.html" itemprop="commentCount"></span></a></span><br><span class="post-meta-item" title="本文字数"><span class="post-meta-item-icon"><i class="fa fa-file-word-o"></i> </span><span class="post-meta-item-text">本文字数：</span> <span>15k</span> </span><span class="post-meta-item" title="阅读时长"><span class="post-meta-item-icon"><i class="fa fa-clock-o"></i> </span><span class="post-meta-item-text">阅读时长 &asymp;</span> <span>14 分钟</span></span></div></header><div class="post-body" itemprop="articleBody"><meta name="referrer" content="no-referrer"><a id="more"></a><p><strong>目录：</strong><br><img src="https://upload-images.jianshu.io/upload_images/15645795-c2db5d64eb91a853.png" alt="表空间目录.png"></p><p><strong>表空间</strong></p><p>什么是表空间：Oracle中的用户都有属于自己的默认的表空间 ，在一段内存空间中存储的大部分是表 ，所以称为表空间。</p><p><strong>用户的表空间</strong></p><p>1,系统用户的表空间</p><p>2,普通用户的表空间</p><p><strong>为什么要给普通用户创建属于自己的表空间那？</strong></p><p>项目中很可能与其他项目使用同一个数据库 ，</p><p>多个用户在使用同一个数据库的时候有可能访问同一个数据库文件 ，</p><p>就会造成资源争用问题 ，给不同的用户指定不同的表空间 ，就可以让他们</p><p>使用不同的数据文件 ，解决争用问题。</p><p>用户有一个缺省的<a href="http://baike.baidu.com/view/70152.htm" target="_blank" rel="noopener">表空间</a> ，但是该用户还可以使用其他的表空间 ，如果我们在创建对象时不指定表空间 ，则对象存储在缺省表空间中 ，要想让对象存储在其他表空间中 ，我们需要在创建对象时指定该对象的表空间</p><h2 id="一、创建表空间语法及给用户表空间"><a href="#一、创建表空间语法及给用户表空间" class="headerlink" title="一、创建表空间语法及给用户表空间"></a>一、创建表空间语法及给用户表空间</h2><p><img src="https://upload-images.jianshu.io/upload_images/15645795-68514643c2a9df3e.png" alt="表空间分类图"></p><p><strong>前提打开cmd输入,进入sqlplus和连接数据库及登录sys。</strong></p><p>登录sys：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">sqlplus sys&#x2F;123456@ZhengJiaAo:1521&#x2F;ORCL as sysdba</span><br></pre></td></tr></table></figure><h4 id="0-查看管理员用户使用的表空间名称列表"><a href="#0-查看管理员用户使用的表空间名称列表" class="headerlink" title="0.查看管理员用户使用的表空间名称列表"></a>0.查看管理员用户使用的表空间名称列表</h4><p>status状态（只读READ ONLY / 读写ONLINE）</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select tablespace_name,status from dba_tablespaces;</span><br><span class="line">SYSTEM                         ONLINE #系统表空间 ，是永久系统表空间 ，用于存储SYS用户的表、视图、存储过程对象。</span><br><span class="line">SYSAUX                         ONLINE #作为EXAMPLE的辅助表空间</span><br><span class="line">UNDOTBS1                       ONLINE #存储撤销信息的undo表空间</span><br><span class="line">TEMP                           ONLINE #临时表空间 ，用户存储SQL语句处理的表示索引信息</span><br><span class="line">USERS                          ONLINE #永久表空间 ，存储数据库用户创建的数据库对象</span><br><span class="line">EXAMPLE                        ONLINE #安装Oracle数据库示例的表空间</span><br></pre></td></tr></table></figure><h4 id="1-创建临时表空间-duke-temp"><a href="#1-创建临时表空间-duke-temp" class="headerlink" title="1.创建临时表空间: duke_temp"></a>1.创建临时表空间: duke_temp</h4><p>先创建好路径 I:\Orcl\tablespace</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line">CREATE TEMPORARY TABLESPACE duke_temp         &#x2F;* 临时表空间名称:duke_temp *&#x2F;</span><br><span class="line">tempfile &#39;I:\Orcl\tablespace\duke_temp.dbf&#39; &#x2F;* 临时表空间路径 *&#x2F;&#x2F;*--oracle文件路径*&#x2F;</span><br><span class="line">size 50m                                      &#x2F;* 空间初始大小：50M*&#x2F;</span><br><span class="line">autoextend ON                                 &#x2F;* 开启自动扩展 *&#x2F;</span><br><span class="line">NEXT 50m                                      &#x2F;* 空间满了一次扩展50M*&#x2F;</span><br><span class="line">maxsize 6G                                    &#x2F;* 空间最终大小6G,可设为 unlimited 大小不受限制*&#x2F;</span><br><span class="line">extent management local;</span><br><span class="line"></span><br><span class="line">表空间已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; select tablespace_name,status from dba_tablespaces;</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                STATUS</span><br><span class="line">------------------------------ ---------</span><br><span class="line">SYSTEM                         ONLINE </span><br><span class="line">SYSAUX                         ONLINE </span><br><span class="line">UNDOTBS1                       ONLINE </span><br><span class="line">TEMP                           ONLINE </span><br><span class="line">USERS                          ONLINE</span><br><span class="line">EXAMPLE                        ONLINE </span><br><span class="line">EBANK_TEMP                     ONLINE</span><br><span class="line">ECIF_DATA                      ONLINE</span><br><span class="line">ECIF_INDEX                     ONLINE</span><br><span class="line">EIP_DATA                       ONLINE</span><br><span class="line">YANG                           ONLINE</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                STATUS</span><br><span class="line">------------------------------ ---------</span><br><span class="line">DUKE_TEMP                      ONLINE &#x2F;*新创建的临时表空间*&#x2F;</span><br><span class="line"></span><br><span class="line">已选择12行。</span><br></pre></td></tr></table></figure><h4 id="2-创建数据表空间-duke-data"><a href="#2-创建数据表空间-duke-data" class="headerlink" title="2.创建数据表空间: duke_data"></a>2.创建数据表空间: duke_data</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line">CREATE TABLESPACE duke_data                   &#x2F;* 数据表空间名称:duke_data *&#x2F;</span><br><span class="line">datafile &#39;I:\Orcl\tablespace\duke_data.dbf&#39; &#x2F;* 数据表空间路径 *&#x2F;</span><br><span class="line">size 50m                                      &#x2F;* 表空间初始大小：50M *&#x2F;</span><br><span class="line">autoextend ON                                 &#x2F;* 开启自动扩展*&#x2F;</span><br><span class="line">NEXT 50m                                      &#x2F;* 表空间满了后一次扩展50M *&#x2F;</span><br><span class="line">maxsize 10G                                   &#x2F;* 空间最终大小10G,可设为 unlimited 大小不受限制*&#x2F;</span><br><span class="line">logging extent management local;</span><br><span class="line"></span><br><span class="line">表空间已创建。</span><br><span class="line"></span><br><span class="line">SQL&gt; select tablespace_name,status from dba_tablespaces;</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                STATUS</span><br><span class="line">------------------------------ ---------</span><br><span class="line">SYSTEM                         ONLINE</span><br><span class="line">SYSAUX                         ONLINE</span><br><span class="line">UNDOTBS1                       ONLINE</span><br><span class="line">TEMP                           ONLINE</span><br><span class="line">USERS                          ONLINE</span><br><span class="line">EXAMPLE                        ONLINE</span><br><span class="line">EBANK_TEMP                     ONLINE</span><br><span class="line">ECIF_DATA                      ONLINE</span><br><span class="line">ECIF_INDEX                     ONLINE</span><br><span class="line">EIP_DATA                       ONLINE</span><br><span class="line">YANG                           ONLINE</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                STATUS</span><br><span class="line">------------------------------ ---------</span><br><span class="line">DUKE_TEMP                      ONLINE &#x2F;*新创建的临时表空间*&#x2F;</span><br><span class="line">DUKE_DATA                      ONLINE &#x2F;*新创建的数据表空间*&#x2F;</span><br><span class="line"></span><br><span class="line">已选择13行。</span><br></pre></td></tr></table></figure><h4 id="3-创建用户并指定表空间"><a href="#3-创建用户并指定表空间" class="headerlink" title="3.创建用户并指定表空间"></a>3.创建用户并指定表空间</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">CREATE USER duke                    &#x2F;* 用户名 *&#x2F;</span><br><span class="line">IDENTIFIED BY duke                  &#x2F;* 密码 *&#x2F;  </span><br><span class="line">DEFAULT TABLESPACE duke_data        &#x2F;* 表空间 *&#x2F; &#x2F;*给用户指定默认表空间*&#x2F;   </span><br><span class="line">TEMPORARY TABLESPACE duke_temp      &#x2F;* 临时表空间 *&#x2F; &#x2F;*默认临时表空间*&#x2F;</span><br><span class="line">;</span><br><span class="line"></span><br><span class="line">用户已创建。</span><br></pre></td></tr></table></figure><h4 id="4-修改用户默认和临时表空间"><a href="#4-修改用户默认和临时表空间" class="headerlink" title="4.修改用户默认和临时表空间"></a>4.修改用户默认和临时表空间</h4><p>为了方便以后数据的备份和恢复 ，仅仅将有价值的数据做备份可以提高效率和节省空间。</p><p>修改用户默认表空间：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ALTER USER duke DEFAULT TABLESPACE USERS; &#x2F;*duke 用户名 ，USERS 默认（DEFAULT）表空间*&#x2F;</span><br><span class="line">用户已更改。</span><br></pre></td></tr></table></figure><p>修改用户临时表空间：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER USER duke temporary TABLESPACE TEMP; &#x2F;*修改临时（temporary）表空间*&#x2F;</span><br></pre></td></tr></table></figure><p><strong>注意</strong>：普通用户没有修改默认表空间的权限 ，但是可以通过授权来实现普通用户也能够修改默认表空间。</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; show user;</span><br><span class="line">USER 为 &quot;SYS&quot;</span><br><span class="line">SQL&gt; select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username&#x3D;&#39;SYSTEM&#39;;</span><br><span class="line">DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE</span><br><span class="line">------------------------------ ------------------------------</span><br><span class="line">SYSTEM                         TEMP</span><br></pre></td></tr></table></figure><h4 id="5-删除空间表"><a href="#5-删除空间表" class="headerlink" title="5.删除空间表"></a>5.删除空间表</h4><p>—-删除非空表空间 ，包含物理文件</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">DROP TABLESPACE duke_data INCLUDING CONTENTS AND DATAFILES;</span><br><span class="line">表空间已经删除。</span><br></pre></td></tr></table></figure><p>删除表空间后 ，原先指向该表空间的用户仍然默认的空间位置 ，<br>需要通过alter user 命令将用户的表空间指向一个有效的表空间。</p><p>删除表空间方法总结：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line">--删除空的表空间 ，但是不包含物理文件</span><br><span class="line">drop tablespace duke_temp</span><br><span class="line"> --删除非空表空间 ，但是不包含物理文件</span><br><span class="line">drop tablespace duke_temp including contents;</span><br><span class="line">--删除空表空间 ，包含物理文件</span><br><span class="line">drop tablespace duke_temp including datafiles;</span><br><span class="line">--删除非空表空间 ，包含物理文件</span><br><span class="line">DROP TABLESPACE duke_data INCLUDING CONTENTS AND DATAFILES;</span><br><span class="line">--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段 ，就要加上CASCADE CONSTRAINTS</span><br><span class="line">drop tablespace duke_temp including contents and datafiles CASCADE CONSTRAINTS;</span><br><span class="line"></span><br><span class="line">--说明： 删除了duke ，只是删除了该duke下的schema objects ，是不会删除相应的表空间（tablespace）的。</span><br><span class="line">drop user duke cascade；  --duke用户</span><br></pre></td></tr></table></figure><h4 id="6-给用户授权登录和撤销权限"><a href="#6-给用户授权登录和撤销权限" class="headerlink" title="6.给用户授权登录和撤销权限"></a>6.给用户授权登录和撤销权限</h4><p>新创建的用户是不能登录的 ，需授权才能登录oracle数据库 ，授予两个系统内置角色权限 ，connect和resource。</p><p>conncet角色使用户可以登录; resource角色使得用户可以程序开发 ，如创建表 ，触发器等。</p><p>授权命令：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">GRANT CONNECT,RESOURCE TO duke;  &#x2F;* duke用户名 --授权为登录oracle权限 ，具有创建 ，修改等基本权限*&#x2F;</span><br><span class="line">grant dba to duke;   &#x2F;*授权dba最高权限*&#x2F;</span><br></pre></td></tr></table></figure><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">登录用户dike</span><br><span class="line">SQL&gt; connect duke&#x2F;duke</span><br><span class="line">SQL&gt; show user;</span><br><span class="line">USER is &quot;DUKE&quot;</span><br><span class="line">SQL&gt;</span><br></pre></td></tr></table></figure><h4 id="7-查看表空间使用情况"><a href="#7-查看表空间使用情况" class="headerlink" title="7.查看表空间使用情况"></a>7.查看表空间使用情况</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SELECT UPPER(F.TABLESPACE_NAME) AS &quot;表空间名称&quot;, </span><br><span class="line">        ROUND(D.AVAILB_BYTES ,2) AS &quot;表空间大小(G)&quot;, </span><br><span class="line">        ROUND(D.MAX_BYTES,2) AS &quot;最终表空间大小(G)&quot;, </span><br><span class="line">        ROUND((D.AVAILB_BYTES - F.USED_BYTES),2) AS &quot;已使用空间(G)&quot;, </span><br><span class="line">        TO_CHAR(ROUND((D.AVAILB_BYTES - F.USED_BYTES) &#x2F; D.AVAILB_BYTES * 100, </span><br><span class="line">            2), &#39;999.99&#39;) AS &quot;使用比&quot;, </span><br><span class="line">        ROUND(F.USED_BYTES, 6) AS &quot;空闲空间(G)&quot;, </span><br><span class="line">        F.MAX_BYTES AS &quot;最大块(M)&quot; </span><br><span class="line"> FROM (</span><br><span class="line">        SELECT TABLESPACE_NAME, </span><br><span class="line">                ROUND(SUM(BYTES) &#x2F; (1024 * 1024 * 1024), 6) USED_BYTES, </span><br><span class="line">                ROUND(MAX(BYTES) &#x2F; (1024 * 1024 * 1024), 6) MAX_BYTES </span><br><span class="line">        FROM SYS.DBA_FREE_SPACE </span><br><span class="line">        GROUP BY TABLESPACE_NAME) F, </span><br><span class="line">      (SELECT DD.TABLESPACE_NAME, </span><br><span class="line">                ROUND(SUM(DD.BYTES) &#x2F; (1024 * 1024 * 1024), 6) AVAILB_BYTES, </span><br><span class="line">            ROUND(SUM(DECODE(DD.MAXBYTES, 0, DD.BYTES, DD.MAXBYTES))&#x2F;(1024*1024*1024),6) MAX_BYTES </span><br><span class="line">       FROM SYS.DBA_DATA_FILES DD </span><br><span class="line">       GROUP BY DD.TABLESPACE_NAME) D </span><br><span class="line">        WHERE D.TABLESPACE_NAME &#x3D; F.TABLESPACE_NAME </span><br><span class="line">ORDER BY 4 DESC</span><br></pre></td></tr></table></figure><h4 id="8-查看普通用户属于哪个表空间"><a href="#8-查看普通用户属于哪个表空间" class="headerlink" title="8.查看普通用户属于哪个表空间"></a>8.查看普通用户属于哪个表空间</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select username,default_tablespace from dba_users where username&#x3D;&#39;SCOTT&#39;; &#x2F;*用户名*&#x2F;</span><br><span class="line"></span><br><span class="line">USERNAME                       DEFAULT_TABLESPACE</span><br><span class="line">------------------------------ ------------------------------</span><br><span class="line">SCOTT						USERS</span><br></pre></td></tr></table></figure><h4 id="9-查看所有表空间"><a href="#9-查看所有表空间" class="headerlink" title="9.查看所有表空间"></a>9.查看所有表空间</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select * from v$tablespace; &#x2F;*包含从控制文件中获取的表空间名称和编号信息*&#x2F;</span><br><span class="line"></span><br><span class="line">       TS# NAME                           INC BIG FLA ENC</span><br><span class="line">---------- ------------------------------ --- --- --- ---</span><br><span class="line">         0 SYSTEM                         YES NO  YES</span><br><span class="line">         1 SYSAUX                         YES NO  YES</span><br><span class="line">         2 UNDOTBS1                       YES NO  YES</span><br><span class="line">         4 USERS                          YES NO  YES</span><br><span class="line">         3 TEMP                           NO  NO  YES</span><br><span class="line">         6 EXAMPLE                        YES NO  YES</span><br><span class="line">         8 EBANK_TEMP                     NO  NO  YES</span><br><span class="line">         9 ECIF_DATA                      YES NO  YES</span><br><span class="line">        10 ECIF_INDEX                     YES NO  YES</span><br><span class="line">        11 EIP_DATA                       YES NO  YES</span><br><span class="line">        13 YANG                           YES NO  YES</span><br><span class="line"></span><br><span class="line">       TS# NAME                           INC BIG FLA ENC</span><br><span class="line">---------- ------------------------------ --- --- --- ---</span><br><span class="line">        14 DUKE_TEMP                      NO  NO  YES</span><br><span class="line">        15 DUKE_DATA                      YES NO  YES</span><br><span class="line"></span><br><span class="line">已选择13行。</span><br></pre></td></tr></table></figure><h4 id="10-查看表空间下所有用户"><a href="#10-查看表空间下所有用户" class="headerlink" title="10.查看表空间下所有用户"></a>10.查看表空间下所有用户</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select distinct s.owner from dba_segments s where s.tablespace_name &#x3D;&#39;USERS&#39;; &#x2F;*USERS表空间下所有用户*&#x2F;</span><br><span class="line"></span><br><span class="line">OWNER</span><br><span class="line">------------------------------</span><br><span class="line">SCOTT</span><br><span class="line">SYSTEM</span><br><span class="line">OE</span><br></pre></td></tr></table></figure><h4 id="11-查看当前用户的表属于哪个表空间"><a href="#11-查看当前用户的表属于哪个表空间" class="headerlink" title="11.查看当前用户的表属于哪个表空间"></a>11.查看当前用户的表属于哪个表空间</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select table_name,tablespace_name from user_all_tables; &#x2F;*查看当前用户的表属于哪些表空间*&#x2F;</span><br><span class="line"></span><br><span class="line">TABLE_NAME                     TABLESPACE_NAME</span><br><span class="line">------------------------------ ------------------------------</span><br><span class="line">MLOG$                          SYSTEM</span><br><span class="line">SLOG$                          SYSTEM</span><br><span class="line">RGROUP$                        SYSTEM</span><br><span class="line">RGCHILD$                       SYSTEM</span><br><span class="line">SYS_IOT_OVER_5140              SYSAUX</span><br><span class="line">RULE_SET_PR$</span><br><span class="line">SYS_IOT_OVER_5146              SYSAUX</span><br><span class="line">RULE_SET_IOT$</span><br><span class="line">SYS_IOT_OVER_5150              SYSAUX</span><br><span class="line">RULE_SET_ROP$</span><br><span class="line">SYS_IOT_OVER_5387              SYSAUX</span><br></pre></td></tr></table></figure><h4 id="12-查看用户的默认表空间和临时表空间"><a href="#12-查看用户的默认表空间和临时表空间" class="headerlink" title="12.查看用户的默认表空间和临时表空间"></a>12.查看用户的默认表空间和临时表空间</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">查看SYSTEM用户的默认表空间和临时表空间</span><br><span class="line">SQL&gt; select DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username&#x3D;&#39;SYSTEM&#39;;</span><br><span class="line"></span><br><span class="line">DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE</span><br><span class="line">------------------------------ ------------------------------</span><br><span class="line">SYSTEM                         TEMP</span><br></pre></td></tr></table></figure><h4 id="默认表空间"><a href="#默认表空间" class="headerlink" title="默认表空间"></a>默认表空间</h4><p>用户在登陆后创建数据库对象时 ，如果没有指定表空间 ，那么这些数据就会存储到默认表空间。</p><h4 id="13-系统表空间dba-tablespaces和用户表空间user-tablespaces"><a href="#13-系统表空间dba-tablespaces和用户表空间user-tablespaces" class="headerlink" title="13.系统表空间dba_tablespaces和用户表空间user_tablespaces"></a>13.系统表空间dba_tablespaces和用户表空间user_tablespaces</h4><p>普通用户不能查看系统表空间dba_tablespaces ，只能查看用户表空间user_tablespaces</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; connect duke&#x2F;duke</span><br><span class="line">已连接。</span><br><span class="line">SQL&gt; show user;</span><br><span class="line">USER 为 &quot;DUKE&quot;</span><br><span class="line">SQL&gt; select tablespace_name from dba_tablespaces;</span><br><span class="line">select tablespace_name from dba_tablespaces</span><br><span class="line">                            *</span><br><span class="line">第 1 行出现错误:</span><br><span class="line">ORA-00942: 表或视图不存在</span><br><span class="line"></span><br><span class="line">SQL&gt; select tablespace_name from user_tablespaces;</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME</span><br><span class="line">------------------------------</span><br><span class="line">SYSTEM</span><br><span class="line">SYSAUX</span><br><span class="line">UNDOTBS1</span><br><span class="line">TEMP</span><br><span class="line">USERS</span><br><span class="line">EXAMPLE</span><br><span class="line">EBANK_TEMP</span><br><span class="line">ECIF_DATA</span><br><span class="line">ECIF_INDEX</span><br><span class="line">EIP_DATA</span><br><span class="line">YANG</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME</span><br><span class="line">------------------------------</span><br><span class="line">DUKE_TEMP</span><br><span class="line">DUKE_DATA</span><br><span class="line"></span><br><span class="line">已选择13行。</span><br></pre></td></tr></table></figure><h4 id="14-查看表空间数据文件的存放路径："><a href="#14-查看表空间数据文件的存放路径：" class="headerlink" title="14.查看表空间数据文件的存放路径："></a><strong>14.查看表空间数据文件的存放路径</strong>：</h4><p>表空间数据文件都存放在永久性表空间中</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; desc dba_data_files; &#x2F;*数据字典dba_data_file用于存放数据文件的属性。*&#x2F;</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> ----------------------------------------- -------- ----------------------------</span><br><span class="line"> FILE_NAME                                          VARCHAR2(513)</span><br><span class="line"> FILE_ID                                            NUMBER</span><br><span class="line"> TABLESPACE_NAME                                    VARCHAR2(30)</span><br><span class="line"> BYTES                                              NUMBER</span><br><span class="line"> BLOCKS                                             NUMBER</span><br><span class="line"> STATUS                                             VARCHAR2(9)</span><br><span class="line"> RELATIVE_FNO                                       NUMBER</span><br><span class="line"> AUTOEXTENSIBLE                                     VARCHAR2(3)</span><br><span class="line"> MAXBYTES                                           NUMBER</span><br><span class="line"> MAXBLOCKS                                          NUMBER</span><br><span class="line"> INCREMENT_BY                                       NUMBER</span><br><span class="line"> USER_BYTES                                         NUMBER</span><br><span class="line"> USER_BLOCKS                                        NUMBER</span><br><span class="line"> ONLINE_STATUS                                      VARCHAR2(7)</span><br><span class="line"></span><br><span class="line">SQL&gt; select file_name from dba_data_files where tablespace_name&#x3D;&#39;DUKE_DATA&#39;;</span><br><span class="line"></span><br><span class="line">FILE_NAME</span><br><span class="line">--------------------------------------------------------------------------------</span><br><span class="line">I:\ORCL\TABLESPACE\DUKE_DATA.DBF   &#x2F;*文件存放的位置*&#x2F;</span><br></pre></td></tr></table></figure><h4 id="15-修改表空间的状态-联机-脱机、只读-读写"><a href="#15-修改表空间的状态-联机-脱机、只读-读写" class="headerlink" title="15.修改表空间的状态(联机/脱机、只读/读写)"></a>15.修改表空间的状态(联机/脱机、只读/读写)</h4><p><strong>设置表空间的联机ONLINE或脱机状态</strong>OFFLINE：表空间的默认状态是联机状态 ，如果表空间是脱机状态 ，不能够使用这个表空间。</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLESPACE tablespace_name ONLINE|OFFLINE;</span><br></pre></td></tr></table></figure><p>例如：DUKE_DATA表空间脱机 ，查看表空间状态</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; ALTER TABLESPACE duke_data OFFLINE; &#x2F;*DUKE_DATA表空间脱机,需要使用大些字母*&#x2F;</span><br><span class="line"></span><br><span class="line">表空间已更改。</span><br><span class="line">SQL&gt; select STATUS from dba_tablespaces where tablespace_name&#x3D;&#39;DUKE_DATA&#39;; &#x2F;*查看表空间状态*&#x2F;</span><br><span class="line"></span><br><span class="line">STATUS</span><br><span class="line">---------</span><br><span class="line">OFFLINE</span><br></pre></td></tr></table></figure><p><strong>设置表空间的只读|可读写状态</strong>：表空间默认为可读写状态read write</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLESPACE duke_data read only|read write;</span><br></pre></td></tr></table></figure><p><strong>注意</strong>：如果表空间状态中拥有可read ，那么表空间就必须是联机状态ONLINE的。</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; ALTER TABLESPACE duke_data read only;</span><br><span class="line">ALTER TABLESPACE duke_data read only</span><br><span class="line">*</span><br><span class="line">第 1 行出现错误:</span><br><span class="line">ORA-01539: 表空间 &#39;DUKE_DATA&#39; 未联机</span><br></pre></td></tr></table></figure><p>表空间联机后更改状态为只读</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt;  ALTER TABLESPACE duke_data ONLINE;</span><br><span class="line"></span><br><span class="line">表空间已更改。</span><br><span class="line"></span><br><span class="line">SQL&gt;  select STATUS from dba_tablespaces where tablespace_name&#x3D;&#39;DUKE_DATA&#39;;&#x2F;*查案状态*&#x2F;</span><br><span class="line"></span><br><span class="line">STATUS</span><br><span class="line">---------</span><br><span class="line">ONLINE</span><br><span class="line"></span><br><span class="line">SQL&gt;  ALTER TABLESPACE duke_data read only;</span><br><span class="line"></span><br><span class="line">表空间已更改。</span><br><span class="line"></span><br><span class="line">select tablespace_name,status from dba_tablespaces where tablespace_name&#x3D;&#39;DUKE_DATA&#39;;  &#x2F;*查看DUKE_DATA表空间状态 ，读写（ONLINE）,DUKE_DATA要大写*&#x2F;</span><br></pre></td></tr></table></figure><h4 id="16-修改表空间的数据文件"><a href="#16-修改表空间的数据文件" class="headerlink" title="16.修改表空间的数据文件"></a>16.修改表空间的数据文件</h4><p><strong>注意</strong>：如果表空间添加数据文件 ，那么表空间就必须是联机状态ONLINE的。</p><p><strong>增加表空间中的数据文件</strong> xx.dbf</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select STATUS from dba_tablespaces where tablespace_name&#x3D;&#39;DUKE_DATA&#39;; &#x2F;*查看状态是否联机*&#x2F;</span><br><span class="line"></span><br><span class="line">STATUS</span><br><span class="line">---------</span><br><span class="line">ONLINE</span><br><span class="line"></span><br><span class="line">SQL&gt; ALTER TABLESPACE duke_data ADD DATAFILE&#39;I:\Orcl\tablespace\duke_data2.dbf&#39; SIZE 50m; &#x2F;*增加数据文件*&#x2F;</span><br><span class="line"></span><br><span class="line">表空间已更改。</span><br><span class="line"></span><br><span class="line">SQL&gt; select file_name from dba_data_fileS where tablespace_name&#x3D;&#39;DUKE_DATA&#39;;</span><br><span class="line">&#x2F;*查看数据文件及文件位置*&#x2F;</span><br><span class="line">FILE_NAME</span><br><span class="line">--------------------------------------------------------------------------------</span><br><span class="line">I:\ORCL\TABLESPACE\DUKE_DATA.DBF</span><br><span class="line">I:\ORCL\TABLESPACE\DUKE_DATA2.DBF</span><br></pre></td></tr></table></figure><p><strong>注意</strong>：如果发现某个表空间存储空间不足时 ，可以为表空间添加新的数据文件 ，扩展表空间大小</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; ALTER TABLESPACE DUKE_DATA ADD DATAFILE &#39;I:\ORCL\TABLESPACE\DUKE_DATA03.dbf&#39; SIZE 2G AUTOEXTEND OFF;  &#x2F;*方法一：增加新的大数据文件 ，达到增大表空间的目的*&#x2F;</span><br><span class="line"></span><br><span class="line">表空间已更改。</span><br><span class="line"></span><br><span class="line">SQL&gt; ALTER TABLESPACE DUKE_DATA   &#x2F;*方法二：增加数据文件*&#x2F;</span><br><span class="line">  2  ADD DATAFILE &#39;I:\ORCL\TABLESPACE\DUKE_DATA04.dbf&#39;</span><br><span class="line">  3  SIZE 100M</span><br><span class="line">  4  AUTOEXTEND ON</span><br><span class="line">  5  NEXT 10M</span><br><span class="line">  6  MAXSIZE 20480M;</span><br><span class="line"></span><br><span class="line">表空间已更改。</span><br></pre></td></tr></table></figure><p><strong>注意</strong>：在添加新的数据文件时 ，如果同名的操作系统已经存在 ，ALTER TABLESPACE语句将失败。如果要覆盖同名的操作系统文件时 ，则必须在后面显示的指定REUSE子句。</p><ul><li><strong>调整数据文件的大小</strong></li></ul><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; ALTER DATABASE DATAFILE &#39;I:\ORCL\TABLESPACE\DUKE_DATA04.dbf&#39; RESIZE 500M;</span><br><span class="line"></span><br><span class="line">数据库已更改。</span><br></pre></td></tr></table></figure><ul><li><strong>删除数据文件</strong></li></ul><p><strong>注意</strong>：不能够删除表空间中的第一个数据文件 ，如果将第一个数据文件删除的话 ，相当于删除了整个表空间。</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; ALTER TABLESPACE DUKE_DATA DROP DATAFILE&#39;I:\ORCL\TABLESPACE\DUKE_DATA04.dbf&#39;;</span><br><span class="line">&#x2F;*删除方式一*&#x2F;</span><br><span class="line">表空间已更改。</span><br></pre></td></tr></table></figure><p><img src="https://upload-images.jianshu.io/upload_images/15645795-b0185f47ea57b3b7.png" alt="数据文件图"></p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; alter tablespace DUKE_DATA  drop datafile &#39;I:\ORCL\TABLESPACE\DUKE_DATA03.dbf&#39;;</span><br><span class="line">&#x2F;*删除方式二*&#x2F;</span><br><span class="line">表空间已更改。</span><br></pre></td></tr></table></figure><p>查看删除文件后 ，duke_data表空间的数据文件</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select file_name from dba_data_fileS where tablespace_name&#x3D;&#39;DUKE_DATA&#39;;</span><br><span class="line"></span><br><span class="line">FILE_NAME</span><br><span class="line">--------------------------------------------------------------------------------</span><br><span class="line">I:\ORCL\TABLESPACE\DUKE_DATA.DBF</span><br><span class="line">I:\ORCL\TABLESPACE\DUKE_DATA2.DBF</span><br></pre></td></tr></table></figure><h4 id="17-删除表空间"><a href="#17-删除表空间" class="headerlink" title="17.删除表空间"></a>17.删除表空间</h4><p>基本语法：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DROP TABLESPAC tablespace_name [INCLUDING CONTENTS];</span><br></pre></td></tr></table></figure><p><strong>注意</strong>：如果你希望在删除表空间的同时将表空间中的数据文件一同删除时 ，需要加上[INCLUDING CONTENTS]</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"> drop tablespace DUKE_DATA including contents;</span><br><span class="line">&#x2F;*删除表空间*&#x2F;</span><br><span class="line"> 表空间已更改。</span><br><span class="line"> select file_name from dba_data_fileS where tablespace_name&#x3D;&#39;DUKE_DATA&#39;;</span><br><span class="line">&#x2F;*查看表空间*&#x2F;</span><br><span class="line"> 没有此DUKE_DATA表空间。</span><br></pre></td></tr></table></figure><h4 id="18-用户表空间限额"><a href="#18-用户表空间限额" class="headerlink" title="18.用户表空间限额"></a>18.用户表空间限额</h4><p><strong>表空间存储限制</strong>是用户在某一个表空间中可以使用的存储空间总数。<br>在创建或修改用户时 ，可以由参数<code>quota</code>指出。若用户在向表空间存储数据时 ，超出了此限额 ，则会产生错误。</p><p><strong>错误信息</strong>：ORA-01536:space quota exceeded for tablespace tablespacename..’。<br>可以通过查询字典<code>dba_ts_quotas</code>查看表空间限额信息。</p><p><strong>查看用户的表空间配额</strong></p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br></pre></td><td class="code"><pre><span class="line">查看所有的用户表空间配额情况</span><br><span class="line">SQL&gt; SELECT * FROM DBA_TS_QUOTAS</span><br><span class="line">  2  ;</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                USERNAME                            BYTES</span><br><span class="line">------------------------------ ------------------------------ ----------</span><br><span class="line"> MAX_BYTES     BLOCKS MAX_BLOCKS DRO</span><br><span class="line">---------- ---------- ---------- ---</span><br><span class="line">SYSAUX                         APPQOSSYS                               0</span><br><span class="line">        -1          0         -1 NO</span><br><span class="line"></span><br><span class="line">SYSAUX                         FLOWS_FILES                             0</span><br><span class="line">        -1          0         -1 NO</span><br><span class="line"></span><br><span class="line">SYSAUX                         SYSMAN                          160038912</span><br><span class="line">        -1      19536         -1 NO</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                USERNAME                            BYTES</span><br><span class="line">------------------------------ ------------------------------ ----------</span><br><span class="line"> MAX_BYTES     BLOCKS MAX_BLOCKS DRO</span><br><span class="line">---------- ---------- ---------- ---</span><br><span class="line">SYSAUX                         OLAPSYS                           4718592</span><br><span class="line">        -1        576         -1 NO</span><br></pre></td></tr></table></figure><p>查看当前用户表空间的配额情况</p><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#查看当前用户表空间的配额情况   </span></span><br><span class="line"><span class="keyword">SELECT</span> * <span class="keyword">FROM</span> USER_TS_QUOTAS;</span><br><span class="line"></span><br><span class="line">SQL&gt; DESC DBA_TS_QUOTAS;</span><br><span class="line"> 名称                                      是否为空? 类型</span><br><span class="line"> <span class="comment">----------------------------------------- -------- ----------------------------</span></span><br><span class="line"> TABLESPACE_NAME                           NOT NULL VARCHAR2(30)</span><br><span class="line"> USERNAME                                  NOT NULL VARCHAR2(30)</span><br><span class="line"> BYTES                                              NUMBER</span><br><span class="line"> MAX_BYTES                                          NUMBER</span><br><span class="line"> BLOCKS                                             NUMBER</span><br><span class="line"> MAX_BLOCKS                                         NUMBER</span><br><span class="line"> DROPPED                                            VARCHAR2(3)</span><br></pre></td></tr></table></figure><p><strong>注意</strong>：若<code>MAX_BYTES=-1</code>表示没有配额限制</p><h2 id="三、oralce-数据库设置表空间的只读-读写模式"><a href="#三、oralce-数据库设置表空间的只读-读写模式" class="headerlink" title="三、oralce 数据库设置表空间的只读/读写模式"></a>三、oralce 数据库设置表空间的只读/读写模式</h2><p>意义：主要是为了确保数据完整性。</p><p>如:数据备份与还原操作、历史数据的完整性保护。可以将表空间设置只读模式。</p><p>只读：是表空间中不能进行INSERT（插入）、UPDATE（修改）、DELETE（删除）等操作。</p><p><strong>需要dba权限的用户登录才可以进行一下操作：</strong></p><h4 id="1-打开cmd输入-进入sqlplus和连接数据库及登录sys。"><a href="#1-打开cmd输入-进入sqlplus和连接数据库及登录sys。" class="headerlink" title="1.打开cmd输入,进入sqlplus和连接数据库及登录sys。"></a>1.打开cmd输入,进入sqlplus和连接数据库及登录sys。</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">sqlplus sys&#x2F;123456@ZhengJiaAo:1521&#x2F;ORCL as sysdba</span><br></pre></td></tr></table></figure><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; select tablespace_name,status from dba_tablespaces;  &#x2F;&#x2F;查看所有表空间状态 ，读写（ONLINE）</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                STATUS</span><br><span class="line">------------------------------ ---------</span><br><span class="line">SYSTEM                         ONLINE</span><br><span class="line">SYSAUX                         ONLINE</span><br><span class="line">UNDOTBS1                       ONLINE</span><br><span class="line">TEMP                           ONLINE</span><br><span class="line">USERS                          ONLINE</span><br><span class="line">EXAMPLE                        ONLINE</span><br><span class="line">EBANK_TEMP                     ONLINE</span><br><span class="line">ECIF_DATA                      ONLINE</span><br><span class="line">ECIF_INDEX                     ONLINE</span><br><span class="line">EIP_DATA                       ONLINE</span><br><span class="line">YANG                           ONLINE</span><br><span class="line"></span><br><span class="line">已选择11行。</span><br></pre></td></tr></table></figure><h4 id="2-更改表空间状态：只读（READ-ONLY）。"><a href="#2-更改表空间状态：只读（READ-ONLY）。" class="headerlink" title="2.更改表空间状态：只读（READ ONLY）。"></a>2.更改表空间状态：只读（READ ONLY）。</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; alter tablespace users read only;</span><br><span class="line"></span><br><span class="line">表空间已更改。</span><br><span class="line"></span><br><span class="line">SQL&gt; select tablespace_name,status from dba_tablespaces;</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                STATUS</span><br><span class="line">------------------------------ ---------</span><br><span class="line">SYSTEM                         ONLINE</span><br><span class="line">SYSAUX                         ONLINE</span><br><span class="line">UNDOTBS1                       ONLINE</span><br><span class="line">TEMP                           ONLINE</span><br><span class="line">USERS                          READ ONLY</span><br><span class="line">EXAMPLE                        ONLINE</span><br><span class="line">EBANK_TEMP                     ONLINE</span><br><span class="line">ECIF_DATA                      ONLINE</span><br><span class="line">ECIF_INDEX                     ONLINE</span><br><span class="line">EIP_DATA                       ONLINE</span><br><span class="line">YANG                           ONLINE</span><br><span class="line"></span><br><span class="line">已选择11行。</span><br></pre></td></tr></table></figure><h4 id="3-更改表空间状态为：读写（ONLINE）。"><a href="#3-更改表空间状态为：读写（ONLINE）。" class="headerlink" title="3.更改表空间状态为：读写（ONLINE）。"></a>3.更改表空间状态为：读写（ONLINE）。</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">SQL&gt; alter tablespace users read write;</span><br><span class="line"></span><br><span class="line">表空间已更改。</span><br><span class="line"></span><br><span class="line">SQL&gt; select tablespace_name,status from dba_tablespaces;</span><br><span class="line"></span><br><span class="line">TABLESPACE_NAME                STATUS</span><br><span class="line">------------------------------ ---------</span><br><span class="line">SYSTEM                         ONLINE</span><br><span class="line">SYSAUX                         ONLINE</span><br><span class="line">UNDOTBS1                       ONLINE</span><br><span class="line">TEMP                           ONLINE</span><br><span class="line">USERS                          ONLINE</span><br><span class="line">EXAMPLE                        ONLINE</span><br><span class="line">EBANK_TEMP                     ONLINE</span><br><span class="line">ECIF_DATA                      ONLINE</span><br><span class="line">ECIF_INDEX                     ONLINE</span><br><span class="line">EIP_DATA                       ONLINE</span><br><span class="line">YANG                           ONLINE</span><br><span class="line"></span><br><span class="line">已选择11行。</span><br></pre></td></tr></table></figure><h2 id="四、Schema和用户"><a href="#四、Schema和用户" class="headerlink" title="四、Schema和用户"></a>四、Schema和用户</h2><p><strong>Oracle的方案（Schema）和用户（User）的区别</strong></p><h3 id="Schema定义："><a href="#Schema定义：" class="headerlink" title="Schema定义："></a>Schema定义：</h3><p>A schema is a collection of database objects (used by a user).<br>Schema objects are the logical structures that directly refer to the database’s data.<br>A user is a name defined in the database that can connect to and access objects。<br>Schemas and users help database administrators manage database security</p><p>模式是数据库对象（由用户使用）的集合。</p><p>Schema对象是直接引用数据库数据的逻辑结构。</p><p>用户是数据库中定义的可以连接和访问对象的名称。</p><p>模式和用户帮助数据库管理员管理数据库安全性</p><p>从定义中我们可以看出schema为数据库对象的集合 ，为了区分各个集合 ，我们需要给这个集合起个名字 ，这些名字就是我们在企业管理器的方案下看到的许多类似用户名的节点 ，这些类似用户名的节点其实就是一个schema ，schema里面包含了各种对象如tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.</p><h4 id="1-一个用户一般对应一个schema："><a href="#1-一个用户一般对应一个schema：" class="headerlink" title="1.一个用户一般对应一个schema："></a>1.一个用户一般对应一个schema：</h4><p>一个用户一般对应一个schema ，该用户的schema名等于用户名 ，并作为该用户缺省schema。这也就是在企业管理器的方案下看到schema名都为数据库用户名的原因。</p><p>数据库Schema有两种含义:一种是概念上的Schema ，指的是一组DDL语句集 ，该语句集完整地描述了数据库的结构。还有一种是物理上的 Schema ，指的是数据库中的一个名字空间 ，它包含一组表、视图和存储过程等命名对象。物理Schema可以通过标准SQL语句来创建、更新和修改。例 如以下SQL语句创建了两个物理Schema：</p><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">create schema SCHEMA_A;</span><br><span class="line">create table SCHEMA_A.CUSTOMERS(ID int not null,……);</span><br></pre></td></tr></table></figure><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">create schema SCHEMA_B;</span><br><span class="line">create table SCHEMA_B.CUSTOMERS(ID int not null,……);</span><br></pre></td></tr></table></figure><p>简单的说：就是一个数据库用户所拥有的数据库的对象。<br>比如scott用户建立了表 ，索引 ，视图 ，存储过程等对象 ，那么这些对象就构成了schema scott</p><p>Oracle数据库中不能新创建一个schema ，要想创建一个schema ，只能通过创建一个用户的方法解决(Oracle中虽然有create schema语句 ，但是它并不是用来创建一个schema的) ，在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema.</p><p>即schema的个数同user的个数相同 ，而且schema名字同user名字一一对应并且相同 ，所有我们可以称schema为user的别名 ，虽然这样说并不准确 ，但是更容易理解一些。</p><h4 id="2-一个用户还可以使用其他的schema"><a href="#2-一个用户还可以使用其他的schema" class="headerlink" title="2.一个用户还可以使用其他的schema:"></a>2.一个用户还可以使用其他的schema:</h4><p>访问一个表时 ，没有指明该表属于哪一个schema中的 ，系统就会自动给我们在表上加上缺省的sheman名.<br>访问scott用户下的emp表 ，通过select * from emp; 其实 ，这sql语句的完整写法为select * from scott.emp;<br>在数据库中一个对象的完整名称为schema.object ，而不属user.object.</p><h4 id="3-oracle中的schema就是指一个用户下所有对象的集合"><a href="#3-oracle中的schema就是指一个用户下所有对象的集合" class="headerlink" title="3.oracle中的schema就是指一个用户下所有对象的集合:"></a>3.oracle中的schema就是指一个用户下所有对象的集合:</h4><p>schema本身不能理解成一个对象 ，oracle并没有提供创建schema的语法 ，schema也并不是在创建user时就创建 ，而是在该用户下创建第一个对象之后schema也随之产生 ，只要user下存在对象 ，schema就一定存在 ，user下如果不存在对象 ，schema也不存在；这一点类似于temp tablespace group ，另外也可以通过oem来观察 ，如果创建一个新用户 ，该用户下如果没有对象则schema不存在 ，如果创建一个对象则和用户同名的schema也随之产生。</p><p><img src="https://upload-images.jianshu.io/upload_images/15645795-60c67264ca681c2e.png" alt="Schema和用户的关系图"></p></div><div><div style="text-align:center;color:#ccc;font-size:14px">-------------本文结束<i class="fa fa-paw"></i>感谢您的阅读-------------</div></div><div class="reward-container"><div><p style="font-size:14px;color:#34495e;margin:0 0 5px 0">赞赏一下吧～ 还可以关注公众号订阅最新内容</p></div><button disable="enable" onclick="var qr = document.getElementById(&quot;qr&quot;); qr.style.display = (qr.style.display === 'none') ? 'block' : 'none';">打赏</button><div id="qr" style="display:none"><div style="display:inline-block"><img src="/my-images/WeChat.png" alt="宇宙小神特别萌 微信支付"><p>微信支付</p></div><div style="display:inline-block"><img src="/my-images/Alipay.png" alt="宇宙小神特别萌 支付宝"><p>支付宝</p></div></div></div><footer class="post-footer"><div class="post-tags"><a href="/tags/Oracle/" rel="tag"><i class="fa fa-tag"></i> Oracle</a></div><div class="post-nav"><div class="post-nav-item"><a href="/posts/1333463013.html" rel="prev" title="springBoot配置Redis多数据源+完整测试"><i class="fa fa-chevron-left"></i> springBoot配置Redis多数据源+完整测试</a></div><div class="post-nav-item"><a href="/posts/2697482281.html" rel="next" title="Zookeeper-配置超级用户(Windows-Linux)">Zookeeper-配置超级用户(Windows-Linux) <i class="fa fa-chevron-right"></i></a></div></div></footer></article></div></div><div class="comments" id="valine-comments"></div><script>window.addEventListener('tabs:register', () => {
    let activeClass = CONFIG.comments.activeClass;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }</script></div><div class="toggle sidebar-toggle"><span class="toggle-line toggle-line-first"></span> <span class="toggle-line toggle-line-middle"></span> <span class="toggle-line toggle-line-last"></span></div><aside class="sidebar"><div class="sidebar-inner"><ul class="sidebar-nav motion-element"><li class="sidebar-nav-toc">文章目录</li><li class="sidebar-nav-overview">站点概览</li></ul><div class="post-toc-wrap sidebar-panel"><div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#一、创建表空间语法及给用户表空间"><span class="nav-text">一、创建表空间语法及给用户表空间</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#0-查看管理员用户使用的表空间名称列表"><span class="nav-text">0.查看管理员用户使用的表空间名称列表</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#1-创建临时表空间-duke-temp"><span class="nav-text">1.创建临时表空间: duke_temp</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#2-创建数据表空间-duke-data"><span class="nav-text">2.创建数据表空间: duke_data</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#3-创建用户并指定表空间"><span class="nav-text">3.创建用户并指定表空间</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#4-修改用户默认和临时表空间"><span class="nav-text">4.修改用户默认和临时表空间</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#5-删除空间表"><span class="nav-text">5.删除空间表</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#6-给用户授权登录和撤销权限"><span class="nav-text">6.给用户授权登录和撤销权限</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#7-查看表空间使用情况"><span class="nav-text">7.查看表空间使用情况</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#8-查看普通用户属于哪个表空间"><span class="nav-text">8.查看普通用户属于哪个表空间</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#9-查看所有表空间"><span class="nav-text">9.查看所有表空间</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#10-查看表空间下所有用户"><span class="nav-text">10.查看表空间下所有用户</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#11-查看当前用户的表属于哪个表空间"><span class="nav-text">11.查看当前用户的表属于哪个表空间</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#12-查看用户的默认表空间和临时表空间"><span class="nav-text">12.查看用户的默认表空间和临时表空间</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#默认表空间"><span class="nav-text">默认表空间</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#13-系统表空间dba-tablespaces和用户表空间user-tablespaces"><span class="nav-text">13.系统表空间dba_tablespaces和用户表空间user_tablespaces</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#14-查看表空间数据文件的存放路径："><span class="nav-text">14.查看表空间数据文件的存放路径：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#15-修改表空间的状态-联机-脱机、只读-读写"><span class="nav-text">15.修改表空间的状态(联机&#x2F;脱机、只读&#x2F;读写)</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#16-修改表空间的数据文件"><span class="nav-text">16.修改表空间的数据文件</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#17-删除表空间"><span class="nav-text">17.删除表空间</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#18-用户表空间限额"><span class="nav-text">18.用户表空间限额</span></a></li></ol></li></ol><li class="nav-item nav-level-2"><a class="nav-link" href="#三、oralce-数据库设置表空间的只读-读写模式"><span class="nav-text">三、oralce 数据库设置表空间的只读&#x2F;读写模式</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#1-打开cmd输入-进入sqlplus和连接数据库及登录sys。"><span class="nav-text">1.打开cmd输入,进入sqlplus和连接数据库及登录sys。</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#2-更改表空间状态：只读（READ-ONLY）。"><span class="nav-text">2.更改表空间状态：只读（READ ONLY）。</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#3-更改表空间状态为：读写（ONLINE）。"><span class="nav-text">3.更改表空间状态为：读写（ONLINE）。</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#四、Schema和用户"><span class="nav-text">四、Schema和用户</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#Schema定义："><span class="nav-text">Schema定义：</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#1-一个用户一般对应一个schema："><span class="nav-text">1.一个用户一般对应一个schema：</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#2-一个用户还可以使用其他的schema"><span class="nav-text">2.一个用户还可以使用其他的schema:</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#3-oracle中的schema就是指一个用户下所有对象的集合"><span class="nav-text">3.oracle中的schema就是指一个用户下所有对象的集合:</span></a></li></ol></li></ol></li></div></div><div class="site-overview-wrap sidebar-panel"><div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person"><img class="site-author-image" itemprop="image" alt="宇宙小神特别萌" src="/my-images/avatar.gif"><p class="site-author-name" itemprop="name">宇宙小神特别萌</p><div class="site-description" itemprop="description">人生就是一次幸运的旅行！</div></div><div class="site-state-wrap motion-element"><nav class="site-state"><div class="site-state-item site-state-posts"><a href="/archives/"><span class="site-state-item-count">116</span> <span class="site-state-item-name">文章</span></a></div><div class="site-state-item site-state-tags"><a href="/tags/"><span class="site-state-item-count">27</span> <span class="site-state-item-name">标签</span></a></div></nav></div><div class="links-of-author motion-element"><span class="links-of-author-item"><a href="https://github.com/zhengjiaao" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;zhengjiaao" rel="noopener" target="_blank"><i class="fa fa-fw fa-github"></i>GitHub</a> </span><span class="links-of-author-item"><a href="https://www.jianshu.com/u/70d69269bd09" title="简书 → https:&#x2F;&#x2F;www.jianshu.com&#x2F;u&#x2F;70d69269bd09" rel="noopener" target="_blank"><i class="fa fa-fw fa-book"></i>简书</a> </span><span class="links-of-author-item"><a href="/atom.xml" title="RSS → &#x2F;atom.xml"><i class="fa fa-fw fa-rss"></i>RSS</a></span></div><div class="links-of-blogroll motion-element"><div class="links-of-blogroll-title"><i class="fa fa-fw fa-link"></i> 推荐阅读</div><ul class="links-of-blogroll-list"><li class="links-of-blogroll-item"><a href="https://github.com/spring-projects" title="https:&#x2F;&#x2F;github.com&#x2F;spring-projects" rel="noopener" target="_blank">spring-projects</a></li><li class="links-of-blogroll-item"><a href="http://www.alloyteam.com/nav/" title="http:&#x2F;&#x2F;www.alloyteam.com&#x2F;nav&#x2F;" rel="noopener" target="_blank">Web前端导航</a></li><li class="links-of-blogroll-item"><a href="http://www.chuangzaoshi.com/code" title="http:&#x2F;&#x2F;www.chuangzaoshi.com&#x2F;code" rel="noopener" target="_blank">创造狮导航</a></li></ul></div></div></div></aside><div id="sidebar-dimmer"></div></div></main><footer class="footer"><div class="footer-inner"><div class="copyright">&copy; <span itemprop="copyrightYear">2020</span> <span class="with-love"><i class="fa fa-heart"></i> </span><span class="author" itemprop="copyrightHolder">宇宙小神特别萌</span> <span class="post-meta-divider">|</span> <span class="post-meta-item-icon"><i class="fa fa-area-chart"></i> </span><span title="站点总字数">1.2m</span> <span class="post-meta-divider">|</span> <span class="post-meta-item-icon"><i class="fa fa-coffee"></i> </span><span title="站点阅读时长">17:41</span></div><div class="busuanzi-count"><script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><span class="post-meta-item" id="busuanzi_container_site_uv" style="display:none"><span class="post-meta-item-icon"><i class="fa fa-user"></i> </span><span class="site-uv" title="总访客量"><span id="busuanzi_value_site_uv"></span> </span></span><span class="post-meta-divider">|</span> <span class="post-meta-item" id="busuanzi_container_site_pv" style="display:none"><span class="post-meta-item-icon"><i class="fa fa-eye"></i> </span><span class="site-pv" title="总访问量"><span id="busuanzi_value_site_pv"></span></span></span></div></div></footer></div><script src="//cdn.jsdelivr.net/npm/animejs@3.1.0/lib/anime.min.js"></script><script src="//cdn.jsdelivr.net/npm/jquery@3/dist/jquery.min.js"></script><script src="//cdn.jsdelivr.net/gh/fancyapps/fancybox@3/dist/jquery.fancybox.min.js"></script><script src="//cdnjs.cloudflare.com/ajax/libs/velocity/1.2.1/velocity.min.js"></script><script src="//cdnjs.cloudflare.com/ajax/libs/velocity/1.2.1/velocity.ui.min.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/schemes/muse.js"></script><script src="/js/next-boot.js"></script><script src="/js/bookmark.js"></script><script>!function(){var t=document.createElement("script"),e=window.location.protocol.split(":")[0];t.src="https"===e?"https://zz.bdstatic.com/linksubmit/push.js":"http://push.zhanzhang.baidu.com/push.js";var s=document.getElementsByTagName("script")[0];s.parentNode.insertBefore(t,s)}()</script><script src="/js/local-search.js"></script><script>NexT.utils.loadComments(document.querySelector('#valine-comments'), () => {
  NexT.utils.getScript('//cdnjs.cloudflare.com/ajax/libs/valine/1.3.10/Valine.min.js', () => {
    <!--var GUEST = ['nick', 'mail', 'link'];-->
    var GUEST = ['nick', 'mail'];
    var guest = 'nick,mail,link';
    guest = guest.split(',').filter(item => {
      return GUEST.includes(item);
    });
    new Valine({
      el: '#valine-comments',
      verify: false,
      notify: false,
      appId: 'OuyHeA0XGL8hK4vgbqL0iERx-gzGzoHsz',
      appKey: 'L58VglKmWULcGK2W2AN7kWzM',
      placeholder: "留下邮箱，有回复时你将收到提醒，邮箱不会被公开。",
      avatar: 'wavatar',
      meta: guest,
      pageSize: '10' || 10,
      visitor: true,
      lang: 'zh-cn' || 'zh-cn',
      path: location.pathname,
      recordIP: true,
      serverURLs: ''
    });
  }, window.Valine);
});</script><script src="/live2dw/lib/L2Dwidget.min.js?094cbace49a39548bed64abff5988b05"></script><script>L2Dwidget.init({pluginRootPath:"live2dw/",pluginJsPath:"lib/",pluginModelPath:"assets/",tagMode:!1,model:{jsonPath:"/live2dw/assets/wanko.model.json"},display:{position:"right",width:150,height:300},mobile:{show:!1},log:!1})</script></body><script type="text/javascript" src="/js/src/activetitle.js"></script></html><!-- rebuild by neat -->